/**
* 1、取收、入、发、到 扫描表的 四合一类型数据
* 2、取运单  网点揽入发 扫描员、扫描网点     集散/中心 到件 扫描员、扫描网点
**/
--取揽收、入仓、网点发件数据，根据运单号取最早
--取集货到件中心到件的数据，根据运单号取最早
with tmp_base_data as (
    select
          flag,
          waybill_no,
          scan_user,
          scan_user_code,
          scan_type,
          scan_time,
          input_time,
          input_site,
          scan_site_code,
          network_type,
          check_flag,
          site_agent_name,
          site_agent_code,
          row_number() over (partition by waybill_no,if(flag in ('taking','warehousing','send'),'taking_warehousing_send','centearrival') order by input_time asc,scan_time asc) rn
    from (
        select 'taking' as flag
             , waybill_no
             , deliver_user       scan_user
             , deliver_user_code  scan_user_code
             , scan_type
             , substr(scan_time, 1, 19)  as scan_time
             , substr(input_time, 1, 19) as input_time
             , input_site
             , scan_site_code
             , network_type --1.加盟商,2.转运中心,3.集散点
             , check_flag
             , site_agent_name
             , site_agent_code
        from jms_dwd.dwd_tab_barscan_taking_base_dt
        where dt ='{{ execution_date | cst_ds }}'
          and check_flag = 6
          and network_type in (4,3)
          and waybill_no like 'JT%'
        union all
        select 'warehousing'             as flag
             , waybill_no
             , deliver_user       scan_user
             , deliver_user_code  scan_user_code
             , scan_type
             , substr(scan_time, 1, 19)  as scan_time
             , substr(input_time, 1, 19) as input_time
             , input_site
             , scan_site_code
             , network_type --1.加盟商,2.转运中心,3.集散点
             , check_flag
             , site_agent_name
             , site_agent_code
        from jms_dwd.dwd_tab_barscan_warehousing_base_dt
        where dt ='{{ execution_date | cst_ds }}'
          and check_flag = 6
          and waybill_no like 'JT%'
        union all
        select 'send'                    as flag
             , waybill_no
             , scan_user
             , scan_user_code
             , scan_type
             , substr(scan_time, 1, 19)  as scan_time
             , substr(input_time, 1, 19) as input_time
             , input_site
             , scan_site_code
             , network_type --1.加盟商,2.转运中心,3.集散点
             , check_flag
             , site_agent_name
             , site_agent_code
        from jms_dwd.dwd_tab_barscan_send_base_dt
        where dt ='{{ execution_date | cst_ds }}'
          and check_flag = 6
          and network_type in (4,3)
          and waybill_no like 'JT%'
        union all
        select
             'arrive' as flag
            , t1.waybill_no
            , t1.scan_user
            , t1.scan_user_code
            , t1.scan_type
            , substr(t1.scan_time,1,19) as scan_time
            , substr(t1.input_time, 1, 19) as input_time
            , t1.scan_site as input_site
            , t1.scan_site_code
            , t1.network_type
            , t1.check_flag
            , t1.site_agent_name
            , t1.site_agent_code
        from jms_dwd.dwd_tab_barscan_arrival_base_dt t1
        join
            ( select
                  waybill_no,scan_site_code,next_station_code
              from jms_dwd.dwd_tab_barscan_send_base_dt
              where dt between date_add('{{ execution_date | cst_ds }}',-2) and '{{ execution_date | cst_ds }}'
                and check_flag = 6 and network_type in (4,3)
                and waybill_no like 'JT%'
            ) t2
        on t1.waybill_no=t2.waybill_no
        and t1.scan_site_code=nvl(t2.next_station_code,t1.scan_site_code)
        where t1.dt ='{{ execution_date | cst_ds }}'
        and t1.network_type in (2,3)
        and t1.check_flag=6
        and t1.waybill_no like 'JT%'
    )
)

   --取出收入发网点名称和扫描员名称以及扫描时间
   --取中心到件的扫描员扫描网点和扫描时间
,tmp_barscan_all_data_group_by as (
  select
       waybill_no
      ,site_agent_name
      ,site_agent_code
      ,max(if(flag in ('taking','warehousing','send'),scan_user_code,null)) as tws_scan_user_code
      ,max(if(flag in ('taking','warehousing','send'),scan_site_code,null)) as tws_scan_site_code
      ,max(if(flag in ('taking','warehousing','send'),input_time,null))     as tws_input_time
      ,max(if(flag = 'arrive',scan_user_code,null))                         as arrive_scan_user_code
      ,max(if(flag = 'arrive',scan_site_code,null))                         as arrive_scan_site_code
      ,max(if(flag = 'arrive',input_time,null))                             as arrive_input_time
  from tmp_base_data where rn=1
  group by waybill_no,site_agent_name,site_agent_code
  having tws_scan_site_code is not null
     and arrive_scan_site_code is not null
)

/**
* 1、取四合一配置表 状态为启用的数据
**/
, tmp_config_table as (
select
    network_code        --扫描网点code
   ,network_name        --扫描网点名称
   ,staff_name          --扫描员
   ,staff_code          --扫描员编码
   ,scan_type           --实际扫描类型
   ,use_network_name    --配置网点名称
   ,use_network_code    --配置网点编码
   ,use_operation_type  --生成扫描类型
   ,receiver_name       --配置业务员
   ,receiver_code       --配置业务员编码
   ,use_scan_type
from jms_dim.dim_scan_network_config_base
where status = 1
group by network_code,network_name,staff_name,staff_code,scan_type,use_network_name,use_network_code,use_operation_type,receiver_name,receiver_code,use_scan_type
)

, tmp_config_table_2 as (
select
    network_code        --扫描网点code
   ,network_name        --扫描网点名称
   ,max(staff_name) as staff_name --扫描员
   ,max(staff_code) as staff_code --扫描员编码
   ,max(scan_type) as scan_type           --实际扫描类型
   ,use_network_name    --配置网点名称
   ,use_network_code    --配置网点编码
   ,max(use_operation_type) as use_operation_type  --生成扫描类型
   ,max(receiver_name) as receiver_name       --配置业务员
   ,max(receiver_code) as receiver_code       --配置业务员编码
   ,max(use_scan_type) as use_scan_type
from jms_dim.dim_scan_network_config_base
where status = 1
group by network_code,network_name,use_network_name,use_network_code
)

/**
* 1、将扫描表和四合一配置表关联，过滤掉收入发扫描或者到件扫描不全的运单
**/
insert overwrite table jms_dm.dm_four_to_one_data_summary_middle
select
   t1.waybill_no
  ,t1.site_agent_name
  ,t1.site_agent_code
  ,nvl(t2.network_code      ,t3.network_code)         as network_code
  ,nvl(t2.network_name      ,t3.network_name)         as network_name
  ,nvl(t2.use_scan_type     ,t3.use_scan_type)        as use_scan_type
  ,nvl(t2.staff_name        ,t3.staff_name)           as staff_name
  ,nvl(t2.staff_code        ,t3.staff_code)           as staff_code
  ,nvl(t2.use_operation_type,t3.use_operation_type)   as use_operation_type
  ,nvl(t2.scan_type         ,t3.scan_type)            as scan_type
  ,nvl(t2.use_network_name  ,t3.use_network_name)     as use_network_name
  ,nvl(t2.use_network_code  ,t3.use_network_code)     as use_network_code
  ,nvl(t2.receiver_name     ,t3.receiver_name)        as receiver_name
  ,nvl(t2.receiver_code     ,t3.receiver_code)        as receiver_code
  ,t1.tws_scan_site_code
  ,t1.arrive_scan_site_code
  ,t1.tws_scan_user_code
  ,t1.arrive_scan_user_code
from tmp_barscan_all_data_group_by  t1
left join tmp_config_table t2
        on t2.use_network_code = t1.tws_scan_site_code
       and t2.network_code     = t1.arrive_scan_site_code
       and t2.receiver_code    = t1.tws_scan_user_code
       and t2.staff_code       = t1.arrive_scan_user_code
left join tmp_config_table_2 t3
       on t1.tws_scan_site_code=t3.use_network_code
      and t1.arrive_scan_site_code=t3.network_code
;

--根据扫描网点、配置网点、配置类型汇总运单数量
insert overwrite table jms_dm.dm_four_to_one_data_summary partition(dt)
select
    '{{ execution_date | cst_ds }}'                    as sum_date
  , network_code                    as oper_network_code      --操作网点code
  , site_agent_code                 as oper_agent_code        --操作代理区code
  , use_scan_type         --配置类型
  , use_network_code                as config_network_code    --配置网点编码
  , staff_name   --操作扫描员名称
  , staff_code    --操作扫描员编号
  , site_agent_name      --操作代理区
  , network_name     --操作网点name
  , scan_type        --实际扫描类型
  , use_operation_type              as config_scan_type       --生成扫描类型
  , use_network_name    --配置网点名称
  , receiver_name      --配置业务员名称
  , receiver_code      --配置业务员编号
  , count(waybill_no)      as bill_cnt
  , '{{ execution_date | cst_ds }}'                    as dt
from jms_dm.dm_four_to_one_data_summary_middle
group by oper_agent_code,oper_network_code,use_scan_type,config_scan_type,config_network_code,staff_name,staff_code,site_agent_name,network_name
,scan_type,use_network_name,receiver_name,receiver_code
distribute by dt
;